package hongvt.quanlynhanvien.database;

import hongvt.quanlynhanvien.model.Employee;
import hongvt.quanlynhanvien.model.Timekeeping;
import hongvt.quanlynhanvien.model.WorkingMode;
import hongvt.quanlynhanvien.utils.Define;
import hongvt.quanlynhanvien.utils.MyLog;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseManager extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "quan_ly_nhan_vien.db";
	private static final int DATAVASE_VERSION = 4;

	private static final String CREATE_EMP = "create table " + Define.TABLE_EMP
			+ "(" + Define.EMP_ID + " integer primary key AUTOINCREMENT ,"
			+ Define.EMP_EMP_ID + " text," + Define.EMP_NAME + " text,"
			+ Define.EMP_GENDER + " integer," + Define.EMP_PHONE + " text);";
	private static final String DROP_EMP = "drop table if exists "
			+ Define.TABLE_EMP;

	private static final String CREATE_MODE = "create table "
			+ Define.TABLE_MODE + "(" + Define.MODE_ID
			+ " integer primary key AUTOINCREMENT ," + Define.MODE_EMP_ID
			+ " text," + Define.MODE_WORKING_MODE + " integer,"
			+ Define.MODE_SALARY + " integer," + Define.MODE_BONUS
			+ " integer);";
	private static final String DROP_MODE = "drop table if exists "
			+ Define.TABLE_MODE;

	private static final String CREATE_TIME = "create table "
			+ Define.TABLE_TIME + "(" + Define.TIME_ID
			+ " integer primary key AUTOINCREMENT," + Define.TIME_EMP_ID
			+ " text," + Define.TIME_DATE + " text);";
	private static final String DROP_TIME = "drop table if exists "
			+ Define.TABLE_TIME;

	public DatabaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATAVASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_MODE);
		db.execSQL(CREATE_EMP);
		db.execSQL(CREATE_TIME);
		Log.i("times", "ok");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		// db.execSQL(DROP_TIME);
		db.execSQL(DROP_EMP);
		db.execSQL(DROP_MODE);
		db.execSQL(DROP_TIME);
		onCreate(db);
	}

	public List<WorkingMode> getListWorkingMode() {
		List<WorkingMode> listWorkingMode = new ArrayList<WorkingMode>();

		SQLiteDatabase mydb = this.getWritableDatabase();
		String sql = "select * from " + Define.TABLE_MODE;
		Cursor cursor = mydb.rawQuery(sql, null);

		if (cursor.moveToFirst()) {
			do {
				try {
					WorkingMode mode = new WorkingMode();
					mode.setId(Long.parseLong(getValue(cursor, Define.MODE_ID)));
					mode.setWorkingMode(Integer.parseInt(getValue(cursor,
							Define.MODE_WORKING_MODE)));
					mode.setEmpId(getValue(cursor, Define.MODE_EMP_ID));
					mode.setSalary(Long.parseLong(getValue(cursor,
							Define.MODE_SALARY)));
					mode.setBonus(Long.parseLong(getValue(cursor,
							Define.MODE_BONUS)));

					listWorkingMode.add(mode);
				} catch (Exception e) {
					e.printStackTrace();
				}
			} while (cursor.moveToNext());
		}

		cursor.close();

		return listWorkingMode;
	}

	public long addNewEmployee(Employee employee) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Define.EMP_NAME, employee.getName());
		values.put(Define.EMP_EMP_ID, employee.getEmpId());
		values.put(Define.EMP_PHONE, employee.getPhone());
		values.put(Define.EMP_GENDER, employee.getGender());

		long id = db.insert(Define.TABLE_EMP, null, values);

		return id;
	}

	public List<Employee> getListEmployees() {
		List<Employee> listEmployees = new ArrayList<Employee>();

		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from " + Define.TABLE_EMP;

		Cursor cursor = db.rawQuery(sql, null);

		if (cursor.moveToFirst()) {
			do {
				try {

					Employee emp = new Employee();
					emp.setId(Integer.parseInt(getValue(cursor, Define.EMP_ID)));
					emp.setEmpId(getValue(cursor, Define.EMP_EMP_ID));
					emp.setName(getValue(cursor, Define.EMP_NAME));
					emp.setPhone(getValue(cursor, Define.EMP_PHONE));
					emp.setGender(Integer.parseInt(getValue(cursor,
							Define.EMP_GENDER)));

					listEmployees.add(emp);

				} catch (Exception e) {
					e.printStackTrace();
				}
			} while (cursor.moveToNext());
		}

		cursor.close();

		return listEmployees;
	}

	public long addNewWorkingMode(WorkingMode workingMode) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Define.MODE_EMP_ID, workingMode.getEmpId());
		values.put(Define.MODE_SALARY, workingMode.getSalary());
		values.put(Define.MODE_BONUS, workingMode.getBonus());
		values.put(Define.MODE_WORKING_MODE, workingMode.getWorkingMode());

		long id = db.insert(Define.TABLE_MODE, null, values);

		return id;
	}

	public WorkingMode getWorkingModeByEmpId(String empId) {
		WorkingMode workingMode = null;

		SQLiteDatabase db = this.getReadableDatabase();

		String query = "select * from " + Define.TABLE_MODE + " where "
				+ Define.MODE_EMP_ID + "='" + empId + "';";

		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			workingMode = new WorkingMode();
			workingMode.setId(Long.parseLong(getValue(cursor, Define.MODE_ID)));
			workingMode.setEmpId(getValue(cursor, Define.MODE_EMP_ID));
			workingMode.setSalary(Long.parseLong(getValue(cursor,
					Define.MODE_SALARY)));
			workingMode.setBonus(Long.parseLong(getValue(cursor,
					Define.MODE_BONUS)));
			workingMode.setWorkingMode(Integer.parseInt(getValue(cursor,
					Define.MODE_WORKING_MODE)));
		}

		cursor.close();

		return workingMode;
	}

	public Employee getEmployeeById(String empId) {
		Employee emp = null;

		SQLiteDatabase db = this.getReadableDatabase();

		String query = "select * from " + Define.TABLE_EMP + " where "
				+ Define.EMP_EMP_ID + "='" + empId + "';";

		MyLog.i("query", query);
		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			emp = new Employee();
			emp.setId(Integer.parseInt(getValue(cursor, Define.EMP_ID)));
			emp.setEmpId(getValue(cursor, Define.EMP_EMP_ID));
			emp.setName(getValue(cursor, Define.EMP_NAME));
			emp.setPhone(getValue(cursor, Define.EMP_PHONE));
			emp.setGender(Integer.parseInt(getValue(cursor, Define.EMP_GENDER)));
		}

		cursor.close();

		return emp;
	}

	public void deleteEmpById(String empId) {
		SQLiteDatabase db = this.getWritableDatabase();

		db.delete(Define.TABLE_EMP, Define.EMP_EMP_ID + "=?",
				new String[] { String.valueOf(empId) });
		db.delete(Define.TABLE_MODE, Define.MODE_EMP_ID + "=?",
				new String[] { String.valueOf(empId) });
		db.delete(Define.TABLE_TIME, Define.TIME_EMP_ID + "=?",
				new String[] {empId});
	}

	private String getValue(Cursor cursor, String key) {
		if (cursor != null) {
			int index = cursor.getColumnIndex(key);
			if (index != -1) {
				return cursor.getString(index);
			}
		}

		return null;
	}

	public void updateEmployee(Employee employee) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Define.EMP_NAME, employee.getName());
		values.put(Define.EMP_PHONE, employee.getPhone());
		values.put(Define.EMP_GENDER, employee.getGender());

		db.update(Define.TABLE_EMP, values, Define.EMP_ID + "=?",
				new String[] { String.valueOf(employee.getId()) });
	}

	public void updateWorkingMode(WorkingMode workingMode) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Define.MODE_SALARY, workingMode.getSalary());
		values.put(Define.MODE_BONUS, workingMode.getBonus());
		values.put(Define.MODE_WORKING_MODE, workingMode.getWorkingMode());

		db.update(Define.TABLE_MODE, values, Define.MODE_EMP_ID + "=?",
				new String[] { String.valueOf(workingMode.getEmpId()) });
	}

	public Cursor getListEmpSalary(String month, String year) {
		Cursor song;
		SQLiteDatabase mydb = this.getWritableDatabase();
		String sql = "select * from  " + Define.TABLE_EMP;
		song = mydb.rawQuery(sql, null);
		return song;
	}

	public List<Timekeeping> getListTimekeepingByDate(int year, int month,
			int dayOfMonth) {
		List<Timekeeping> listTimekeepings = new ArrayList<Timekeeping>();

		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(Calendar.DAY_OF_MONTH, dayOfMonth);
		cal.set(Calendar.MONTH, month);
		cal.set(Calendar.YEAR, year);

		Date date = cal.getTime();

		SimpleDateFormat sfd = new SimpleDateFormat(
				Define.TIME_FORMAT_YYYY_MM_DD);
		String compareValue = sfd.format(date);

		MyLog.i("date", compareValue);

		SQLiteDatabase db = this.getReadableDatabase();
		String query = "select * from " + Define.TABLE_TIME + " where "
				+ Define.TIME_DATE + " like " + "'" + compareValue + "';";

		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			do {
				try {
					Timekeeping tk = new Timekeeping();
					tk.setId(Long.parseLong(getValue(cursor, Define.TIME_ID)));
					tk.setEmpId(getValue(cursor, Define.TIME_EMP_ID));
					tk.setDate(getValue(cursor, Define.TIME_DATE));

					listTimekeepings.add(tk);
				} catch (Exception e) {
					e.printStackTrace();
				}
			} while (cursor.moveToNext());
		}
		return listTimekeepings;
	}

	public List<Timekeeping> getListTimekeepingByMonth(int year, int month) {
		List<Timekeeping> listTimekeepings = new ArrayList<Timekeeping>();

		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(Calendar.DAY_OF_MONTH, 1);
		cal.set(Calendar.MONTH, month);
		cal.set(Calendar.YEAR, year);

		Date date = cal.getTime();

		SimpleDateFormat sfd = new SimpleDateFormat(Define.TIME_FORMAT_YYYY_MM);
		String compareValue = sfd.format(date);

		MyLog.i("date", compareValue);

		SQLiteDatabase db = this.getReadableDatabase();
		String query = "select e." + Define.EMP_NAME + ", t."
				+ Define.TIME_EMP_ID + ", count(1) num " + "from "
				+ Define.TABLE_TIME + " t " + "left join " + Define.TABLE_EMP
				+ " e " + "on t." + Define.TIME_EMP_ID + " = e."
				+ Define.EMP_EMP_ID + " where t." + Define.TIME_DATE + " like "
				+ "'" + compareValue + "%'" + " group by e." + Define.EMP_NAME
				+ ", t." + Define.TIME_EMP_ID + ";";
		Log.i("sql", query);
		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			do {
				try {
					Timekeeping tk = new Timekeeping();
					// tk.setId(Long.parseLong(getValue(cursor,
					// Define.TIME_ID)));
					tk.setEmpId(getValue(cursor, Define.TIME_EMP_ID));
					// tk.setDate(getValue(cursor, Define.TIME_DATE));
					tk.setName(getValue(cursor, Define.EMP_NAME));
					tk.setQuan(Integer.parseInt(getValue(cursor, "num")));

					listTimekeepings.add(tk);
				} catch (Exception e) {
					e.printStackTrace();
				}
			} while (cursor.moveToNext());
		}

		cursor.close();

		return listTimekeepings;

	}

	public void insertListTimekeeping(List<Timekeeping> listTimekeepings) {
		SQLiteDatabase db = this.getWritableDatabase();

		for (Timekeeping tk : listTimekeepings) {
			ContentValues values = new ContentValues();
			values.put(Define.TIME_EMP_ID, tk.getEmpId());
			values.put(Define.TIME_DATE, tk.getDate());

			db.insert(Define.TABLE_TIME, null, values);
		}
	}

	public void deleteListTimekeeping(List<Timekeeping> listTimekeepings) {
		SQLiteDatabase db = this.getWritableDatabase();

		for (Timekeeping tk : listTimekeepings) {
			db.delete(Define.TABLE_TIME, Define.TIME_ID + "=?",
					new String[] { String.valueOf(tk.getId()) });
		}
	}

}
